package com.vast.system.utils;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

public class ExcelUtils {

    /**
     * 读取Excel xlsx
     * @param file 文件
     * @param excelOption excel配置项
     *                    excelOption[0] = new String[] {"ip", "类型", "上传时间"};
     *                    excelOption[1] = new String[] {"ip", "type", "uploadDate"};
     * @param eClass 输出实体类型
     * @param skipRows 跳过行数
     * @param <E>
     * @return
     */
    public static <E> List<E> readExcelFile(File file, String[][] excelOption, Class<E> eClass, Integer skipRows) throws InvocationTargetException, IllegalAccessException, InstantiationException, IOException {
        List<E> result = new ArrayList<>();
        try (InputStream is = new FileInputStream(file)) {
            result = readExcel(is, excelOption, eClass, skipRows);
        }
        return result;
    }

    /**
     * 读取上传的excel xlsx
     * @param file 上传文件
     * @param excelOption excel配置项
     *                    excelOption[0] = new String[] {"ip", "类型", "上传时间"};
     *                    excelOption[1] = new String[] {"ip", "type", "uploadDate"};
     * @param eClass 输出实体类型
     * @param skipRows 跳过行数
     * @param <E>
     * @return
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws IOException
     */
    public static <E> List<E> readExcelMultipartFile(MultipartFile file, String[][] excelOption, Class<E> eClass, Integer skipRows) throws InvocationTargetException, IllegalAccessException, InstantiationException, IOException {
        List<E> result = new ArrayList<>();
        try (InputStream is = file.getInputStream()) {
            result = readExcel(is, excelOption, eClass, skipRows);
        }
        return result;
    }

    /**
     * 读取Excel xlsx
     * @param inputStream 文件流
     * @param excelOption excel配置项
     *                    excelOption[0] = new String[] {"ip", "类型", "上传时间"};
     *                    excelOption[1] = new String[] {"ip", "type", "uploadDate"};
     * @param eClass 输出实体类型
     * @param skipRows 跳过行数
     * @param <E>
     * @return
     */
    private static <E> List<E> readExcel(InputStream inputStream, String[][] excelOption, Class<E> eClass, Integer skipRows) throws InvocationTargetException, IllegalAccessException, InstantiationException, IOException {
        List<E> result = new ArrayList<>();
        Map<String, Method> methodMap = new HashMap<>();
        for (Method method : eClass.getMethods()) {
            methodMap.put(method.getName(), method);
        }
        Workbook workbook = WorkbookFactory.create(inputStream);
        //工作表对象
        Sheet sheet = workbook.getSheetAt(0);
        //总行数
        int rowLength = sheet.getLastRowNum();
        for (int i = skipRows; i <= rowLength; i ++) {
            //工作表的列
            Row row = sheet.getRow(i);
            E e = eClass.newInstance();
            for (int j = 0; j < excelOption[1].length; j ++) {
                String col = excelOption[1][j];
                if (col == null) {
                    continue;
                }
                String methodNameSuffix = col.substring(0, 1).toUpperCase() + col.substring(1);
                Method getMethod = methodMap.get("get" + methodNameSuffix);
                Method setMethod = methodMap.get("set" + methodNameSuffix);
                if (getMethod.getReturnType() == String.class) {
                    setMethod.invoke(e, row.getCell(j).getStringCellValue());
                } else if (getMethod.getReturnType() == Integer.class) {
                    setMethod.invoke(e, new Double(row.getCell(j).getNumericCellValue()).intValue());
                } else if (getMethod.getReturnType() == Long.class) {
                    setMethod.invoke(e, new Double(row.getCell(j).getStringCellValue()).longValue());
                } else if (getMethod.getReturnType() == Double.class) {
                    setMethod.invoke(e, row.getCell(j).getNumericCellValue());
                } else if (getMethod.getReturnType() == Date.class) {
                    setMethod.invoke(e, row.getCell(j).getDateCellValue());
                } else {

                }
            }
            result.add(e);
        }
        workbook.close();
        return result;
    }

    /**
     * 写入Excel
     * @param filePath
     * @param data
     * @param excelOption
     * @param eClass
     * @param <E>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws IOException
     */
    public static <E> void writeExcelFile(String filePath, List<E> data, String[][] excelOption, Class<E> eClass) throws InvocationTargetException, IllegalAccessException, IOException {
        //输出Excel文件
        try (FileOutputStream o = new FileOutputStream(filePath)) {
            writeExcel(data ,excelOption, eClass).write(o);
            o.flush();
        }
    }

    /**
     * 写入Excel
     * @param data
     * @param excelOption
     * @param eClass
     * @param <E>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws IOException
     */
    private static <E> XSSFWorkbook writeExcel(List<E> data, String[][] excelOption, Class<E> eClass) throws InvocationTargetException, IllegalAccessException {
        Map<String, Method> methodMap = new HashMap<>();
        for (Method method : eClass.getMethods()) {
            methodMap.put(method.getName(), method);
        }
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建HSSFSheet对象
        XSSFSheet sheet = wb.createSheet();
        //生成title
        XSSFRow titleRow = sheet.createRow(0);
        for (int i = 0; i < excelOption[0].length; i ++) {
            titleRow.createCell(i).setCellValue(excelOption[0][i]);
        }
        for (int i = 0; i < data.size(); i ++) {
            XSSFRow row = sheet.createRow(i + 1);
            for (int j = 0; j < excelOption[1].length; j ++) {
                String col = excelOption[1][j];
                String methodNameSuffix = col.substring(0, 1).toUpperCase() + col.substring(1);
                Method getMethod = methodMap.get("get" + methodNameSuffix);
                Object obj = getMethod.invoke(data.get(i));
                if (obj == null) {
                    continue;
                }
                String objStr = obj.toString();
                if (getMethod.getReturnType() == String.class) {
                    row.createCell(j).setCellValue(objStr);
                } else if (getMethod.getReturnType() == Integer.class) {
                    row.createCell(j).setCellValue(Double.parseDouble(objStr));
                } else if (getMethod.getReturnType() == Long.class) {
                    row.createCell(j).setCellValue(Double.parseDouble(objStr));
                } else if (getMethod.getReturnType() == Double.class) {
                    row.createCell(j).setCellValue(Double.parseDouble(objStr));
                } else if (getMethod.getReturnType() == Date.class) {
                    XSSFCell xssfCell = row.createCell(j);
                    CellStyle style = wb.createCellStyle();
                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                    xssfCell.setCellStyle(style);
                    xssfCell.setCellValue((Date) obj);
                } else {
                    row.createCell(j).setCellValue(objStr);
                }
            }
        }
        return wb;
    }

    /**
     * 导出Excel
     * @param httpServletResponse
     * @param fileName 导出文件名称
     * @param data
     * @param excelOption
     * @param eClass
     * @param <E>
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     * @throws IOException
     */
    public static <E> void exportExcel(HttpServletResponse httpServletResponse, String fileName, List<E> data, String[][] excelOption, Class<E> eClass) throws InvocationTargetException, IllegalAccessException, IOException {
        try (OutputStream os = httpServletResponse.getOutputStream()) {
            XSSFWorkbook wb = writeExcel(data, excelOption, eClass);
            httpServletResponse.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            httpServletResponse.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            httpServletResponse.flushBuffer();
            wb.write(os);
        }
    }

}
